Restoring an Oracle vault to another server

An Oracle vault can be moved to another computer (for example, as a server upgrade) by restoring a backup of the vault made on the original server so long as the vault folder names and paths remain the same.

Warning    It is not possible to move (by restoring from backup) a vault from a pre-Windows Server 2008 computer (including Windows XP, Windows Server 2000, or Windows Server 2003) to a post-Windows Server 2008 (including Windows Vista) or later computer without adverse side effects. The possible side effects include folders and documents not being accessible, and vault corruption. The cause is Windows API functions that behave differently between pre-Windows Server 2008 and post-Windows Server 2008 operating systems.

The only supported method to move an existing vault from a pre-Windows Server 2008 computer to a post-Windows Server 2008 computer is by importing it from the source server into a new vault on the post-Windows Server 2008 computer.

To restore an Oracle vault to another server:

1. On the existing Meridian server, back up the existing vault.
2. On the existing Oracle server:

1. Back up the vault data
2. Check for uncommitted EDM Server transaction log files. There must not be any uncommitted log files before exporting the Oracle data later in this procedure. Check using one of the following methods:

• Check the vault root folder on the Meridian server. The folder should not contain any log files
• Check the Oracle table named <vault name truncated to 11 characters>_LOGFILES. The table should be empty. This is the preferred method.

3. Export the Oracle vault data. Unfortunately, the Oracle exp tool does not support parameters to export triggers, sequences, and so on. Likewise, the TABLES parameter will not export objects like triggers, views, sequences, and so on. The only way for a dump file to contain the sequences and triggers is to export the full contents of the database or export the entire schema named <owner>. If you specify the parameter FULL=Y in an export, you will get all triggers, procedures, views, sequences, and so on. If you specify OWNER=<user name> as a parameter to an export, you will get all objects that are owned by that user, including triggers.
   
   With Oracle 10g or later, you can choose between using the old imp and exp tools or the new Datapump tools named expdmp and impdmp. These new tools introduce much-needed performance improvements, network-based exports and imports, and so on. If you use an export script, make sure you log the output to a log file. All DDL statements in the export dump will be logged. Check that the following data and structures have been exported:

• Sequences and indexes
• Tables
• AM_DATASTORES: Common table for all vaults belongs to the MERIDIAN schema

4. Copy the .DMP file to a safe location on the new server from which to import it later.

3. On the new Oracle server:

1. Create and configure the new Oracle instance, if necessary, with the Oracle Database Configuration Assistant or character mode scripts.
   
   Note    Before importing the dump file, you must first create the tablespaces. Otherwise, the import will create the corresponding data files in the same file structure as the source database, which may not be compatible with the file structure on the new system. If the folder structure for tablespaces is different on the new server, you must create the tablespaces first.
   

2. Create the schema for the IC Meridian vault with user name MERIDIAN and password MANAGER. Following is an example script:

sqlplus SYS/ORATEST@ORATEST AS SYSDBA
CREATE USER "MERIDIAN" PROFILE "DEFAULT" IDENTIFIED BY "MANAGER" DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
GRANT CREATE ANY PROCEDURE TO "MERIDIAN"
GRANT CREATE ANY SEQUENCE TO "MERIDIAN"
GRANT CREATE ANY TABLE TO "MERIDIAN"
GRANT CREATE SESSION TO "MERIDIAN"
GRANT UNLIMITED TABLESPACE TO "MERIDIAN"
GRANT "CONNECT" TO "MERIDIAN"
GRANT "DBA" TO "MERIDIAN"

3. Check that the vault-related data and structures are present in the .DMP file created in step 2.c by using the SHOW=Y parameter with the imp tool. This information is needed to re-create vault-related sequences after importing. Following is an example script.

set HOMEVAULTDIR="D:\AMM\IC-Meridian vaults"
set DUMPFILE=%HOMEVAULTDIR%\Meridian.dmp
set LOGFILE=%HOMEVAULTDIR%\ShowMeridian.log
rem Show TABLES for ALL vaults (SCHEMA)
imp USERID="""SYS/ORATEST@ORATEST as SYSDBA""" SHOW=Y FROMUSER=MERIDIAN TOUSER=MERIDIAN 
FILE="""%DUMPFILE%""" LOG="""%LOGFILE%"""

4. Restore vault data on the new Oracle instance using the dump file created in step 2.c. You need to restore:

• The vault-related table set (18 tables for each vault). Vault-related table names have prefixes that match the vault name truncated to not more than 11 characters.
• The AM-DATASTORE table. This table is common to all vaults in the schema. Each row in this table defines the table name prefix to vault name mapping for each vault.
• Vault-related sequences and indexes.

Following is an example script.

set HOMEVAULTDIR="D:\IC-Meridian Vaults"
set DUMPFILE=%HOMEVAULTDIR%\Meridian.dmp
set LOGFILE=%HOMEVAULTDIR%\ImpMeridian.log
rem Show TABLES for ONE vault ONLY
imp USERID="""SYS/ORATEST@ORATEST as SYSDBA""" FROMUSER=MERIDIAN  
TOUSER=MERIDIAN TABLES=(TESTORAVAUL%%, AM_DATASTORES) 
FILE="""%DUMPFILE%""" LOG="""%LOGFILE%"""

5. Create, re-create, or alter the vault-related sequences. Sequence numbers need to be set or reset to the value captured in the exported dump file as reported in step 3.c. Following is an example script.

create sequence "MERIDIAN".TESTORAVAUL_COMPARTMENTNUMBER
minvalue 1 maxvalue 999999999999999999999999999
increment by 1 start with 2041 noorder nocycle ;

4. On the existing Meridian server, change the value of the following registry key to specify the new Oracle instance:
   

HKEY_LOCAL_MACHINE\SOFTWARE\Cyco\AutoManager Meridian\
CurrentVersion\Installed DataStores\<vaultname>\
CompoundItemService\InstanceName

Tip    The Oracle server name and port number can be specified in InstanceName in the form //<ComputerName>:<Port>/<ServiceName>. This is called EZCONNECT and should be configured in the sqlnet.ora file to work properly. This method is convenient in that you do not have to create and manage a tnsnames.ora file to configure the Oracle database connection.

5. Repeat this procedure for each vault to be restored.